{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = 'E:/工作文件/运营管理部 项目指标/工作量/全量数据/2024-05-01至2024-05-31日报.xlsx'\n",
    "\n",
    "df = pd.read_excel(path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# file_content中将“分类一”等于“请假”的内容过滤掉\n",
    "df = df[df['分类一'] != '请假']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>员工</th>\n",
       "      <th>工作时间</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>宋超</td>\n",
       "      <td>220.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>郝泽霖</td>\n",
       "      <td>210.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>陈峰高</td>\n",
       "      <td>207.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>张思文</td>\n",
       "      <td>204.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>张文鹤</td>\n",
       "      <td>204.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>段笛</td>\n",
       "      <td>199.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>滕昊</td>\n",
       "      <td>196.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>吴冰川</td>\n",
       "      <td>196.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>徐云涛</td>\n",
       "      <td>195.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>刘大铭B</td>\n",
       "      <td>190.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>杨添斯</td>\n",
       "      <td>188.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>齐钢</td>\n",
       "      <td>188.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>徐鹏超</td>\n",
       "      <td>187.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>郑学文</td>\n",
       "      <td>186.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>姜斌</td>\n",
       "      <td>186.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>高林</td>\n",
       "      <td>185.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>王思涵</td>\n",
       "      <td>182.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>周鹏</td>\n",
       "      <td>182.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>李爽</td>\n",
       "      <td>182.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>郑文</td>\n",
       "      <td>181.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>张书译</td>\n",
       "      <td>180.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>刘鑫添</td>\n",
       "      <td>180.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>汪文博</td>\n",
       "      <td>178.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>于凯奇</td>\n",
       "      <td>177.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>王奎</td>\n",
       "      <td>173.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>刘喆</td>\n",
       "      <td>168.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>康玉龙</td>\n",
       "      <td>165.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>蒋伟</td>\n",
       "      <td>162.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>孙舟恩琪</td>\n",
       "      <td>156.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>徐辉</td>\n",
       "      <td>146.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>韩品佳</td>\n",
       "      <td>143.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>孔泽成</td>\n",
       "      <td>108.37</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      员工    工作时间\n",
       "9     宋超  220.26\n",
       "27   郝泽霖  210.09\n",
       "28   陈峰高  207.38\n",
       "12   张思文  204.76\n",
       "13   张文鹤  204.55\n",
       "19    段笛  199.97\n",
       "21    滕昊  196.90\n",
       "4    吴冰川  196.54\n",
       "14   徐云涛  195.59\n",
       "2   刘大铭B  190.79\n",
       "18   杨添斯  188.80\n",
       "31    齐钢  188.05\n",
       "16   徐鹏超  187.24\n",
       "25   郑学文  186.69\n",
       "6     姜斌  186.50\n",
       "30    高林  185.07\n",
       "23   王思涵  182.80\n",
       "5     周鹏  182.66\n",
       "17    李爽  182.46\n",
       "26    郑文  181.48\n",
       "11   张书译  180.35\n",
       "3    刘鑫添  180.17\n",
       "20   汪文博  178.74\n",
       "0    于凯奇  177.43\n",
       "22    王奎  173.20\n",
       "1     刘喆  168.12\n",
       "10   康玉龙  165.50\n",
       "24    蒋伟  162.70\n",
       "8   孙舟恩琪  156.60\n",
       "15    徐辉  146.39\n",
       "29   韩品佳  143.66\n",
       "7    孔泽成  108.37"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_group = df.groupby('员工')['工作时间'].sum()\n",
    "df_group = df_group.reset_index()\n",
    "\n",
    "# 按“工作时间”降序排列\n",
    "df_group = df_group.sort_values(by='工作时间', ascending=False)\n",
    "\n",
    "df_group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_group.to_excel('E:/工作文件/运营管理部 项目指标/工作量/全量数据/汇总-合规.xlsx', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = 'C:/Users/pc/Downloads/5月份日报数据.csv'\n",
    "df = pd.read_csv(path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户</th>\n",
       "      <th>小时</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>宋 超</td>\n",
       "      <td>220.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>郝 泽霖</td>\n",
       "      <td>210.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>陈 峰高</td>\n",
       "      <td>207.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>张 思文</td>\n",
       "      <td>204.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>张 文鹤</td>\n",
       "      <td>204.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>段 笛</td>\n",
       "      <td>199.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>吴 冰川</td>\n",
       "      <td>196.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>徐 云涛</td>\n",
       "      <td>195.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>刘大 铭B</td>\n",
       "      <td>190.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>杨 添斯</td>\n",
       "      <td>188.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>齐 钢</td>\n",
       "      <td>188.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>徐 鹏超</td>\n",
       "      <td>187.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>郑 学文</td>\n",
       "      <td>186.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>姜 斌</td>\n",
       "      <td>186.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>高 林</td>\n",
       "      <td>185.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>王 思涵</td>\n",
       "      <td>182.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>周 鹏</td>\n",
       "      <td>182.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>李 爽</td>\n",
       "      <td>182.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>郑 文</td>\n",
       "      <td>181.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>张 书译</td>\n",
       "      <td>180.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>刘 鑫添</td>\n",
       "      <td>180.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>汪 文博</td>\n",
       "      <td>178.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>于 凯奇</td>\n",
       "      <td>177.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>王 奎</td>\n",
       "      <td>173.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>刘 喆</td>\n",
       "      <td>168.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>康 玉龙</td>\n",
       "      <td>165.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>蒋 伟</td>\n",
       "      <td>162.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>孙舟 恩琪</td>\n",
       "      <td>156.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>徐 辉</td>\n",
       "      <td>146.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>韩 品佳</td>\n",
       "      <td>143.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>滕 昊</td>\n",
       "      <td>108.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>孔 泽成</td>\n",
       "      <td>108.37</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       用户      小时\n",
       "9     宋 超  220.26\n",
       "27   郝 泽霖  210.09\n",
       "28   陈 峰高  207.38\n",
       "12   张 思文  204.76\n",
       "13   张 文鹤  204.55\n",
       "19    段 笛  199.97\n",
       "4    吴 冰川  196.54\n",
       "14   徐 云涛  195.59\n",
       "3   刘大 铭B  190.79\n",
       "18   杨 添斯  188.80\n",
       "31    齐 钢  188.05\n",
       "16   徐 鹏超  187.24\n",
       "25   郑 学文  186.69\n",
       "6     姜 斌  186.50\n",
       "30    高 林  185.07\n",
       "23   王 思涵  182.80\n",
       "5     周 鹏  182.66\n",
       "17    李 爽  182.46\n",
       "26    郑 文  181.48\n",
       "11   张 书译  180.35\n",
       "2    刘 鑫添  180.17\n",
       "20   汪 文博  178.74\n",
       "0    于 凯奇  177.43\n",
       "22    王 奎  173.20\n",
       "1     刘 喆  168.12\n",
       "10   康 玉龙  165.50\n",
       "24    蒋 伟  162.70\n",
       "8   孙舟 恩琪  156.60\n",
       "15    徐 辉  146.39\n",
       "29   韩 品佳  143.66\n",
       "21    滕 昊  108.94\n",
       "7    孔 泽成  108.37"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_grouped = df.groupby('用户')['小时'].sum()\n",
    "df_grouped = df_grouped.reset_index()\n",
    "df_grouped = df_grouped.sort_values(by='小时', ascending=False)\n",
    "\n",
    "df_grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_grouped.to_excel('E:/工作文件/运营管理部 项目指标/工作量/全量数据/汇总-Redmine.xlsx', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
